﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace MO
{
    public class MySqlDbOperation: IDbOperation
    {
        string connstr;
        static Dictionary<Type, SqlDbType> typeDict = new Dictionary<Type, SqlDbType>();
        static MySqlDbOperation()
        {
            typeDict[typeof(int)] = SqlDbType.Decimal;
            typeDict[typeof(string)] = SqlDbType.VarChar;
            typeDict[typeof(DateTime)] = SqlDbType.DateTime;
        }
        public MySqlDbOperation(string connstr)
        {
            this.connstr = connstr;

        }
        public void Dispose()
        {
        }

        public int BatchInsert(System.Data.DataTable table)
        {
            throw new NotImplementedException();
        }

        public int BatchUpdate(System.Data.DataTable table, List<string> keys)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 执行SQL语句。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回影响的行数。</returns>
        public int ExecuteQuery(string cmdText, params object[] args)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteNonQuery();
            }

        }
        /// <summary>
        /// 查出某项数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回第0行第0项数据。</returns>
        public object ExecuteObject(string cmdText, params object[] args)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteScalar();
            }

        }
        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回DataReader。</returns>
        public DbDataReader ExecuteReader(string cmdText, params object[] args)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteReader();
            }
        }
        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回DataTable。</returns>
        public DataTable ExecuteDataTable(string cmdText, params object[] args)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                DataTable result = new DataTable();
                MySqlDataAdapter oda = new MySqlDataAdapter();
                oda.SelectCommand = Format(cmdText, conn, args);
                oda.Fill(result);
                return result;
            }

        }
        /// <summary>
        /// 生成插入语句。
        /// </summary>
        /// <param name="table">表名。</param>
        /// <param name="columns">插入的字段。</param>
        /// <returns>插入语句字符串。</returns>
        public int Insert(string table, Dictionary<string, object> columns)
        {
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                List<string> numbers = new List<string>();
                for (int i = 0; i < columns.Count; i++)
                    numbers.Add(string.Concat("{", i, "}"));
                var command = Format(string.Format("insert into {0} ({1}) values ({2})", table, string.Join(",", columns.Keys.ToArray()), string.Join(",", numbers.ToArray())),
                    conn,
                    columns.Values.ToArray());
                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 生成更新语句。
        /// </summary>
        /// <param name="table">表名。</param>
        /// <param name="columns">更新和条件的数据，先添加更新数据，加完后，添加"\r"进行分隔，然后添加条件数据。</param>
        /// <returns>更新语句字符串。</returns>
        public int Update(string table, Dictionary<string, object> columns)
        {

            List<string> contents = new List<string>();
            foreach (string key in columns.Keys)
            {
                if (key == "\r")
                    break;
                contents.Add(key);
            }
            columns.Remove("\r");
            List<string> conditions = new List<string>();
            foreach (string key in columns.Keys)
            {
                if (!contents.Contains(key))
                    conditions.Add(key);
            }
            for (int i = 0; i < columns.Count; i++)
            {
                if (i < contents.Count)
                    contents[i] += string.Concat("={", i, "}");
                else
                    conditions[i - contents.Count] += string.Concat("={", i, "}");
            }
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                var command = Format(string.Format("update {0} set {1} where {2}", table, string.Join(",", contents.ToArray()), string.Join(" and ", conditions.ToArray())),
                    conn,
                    columns.Values.ToArray());
                return command.ExecuteNonQuery();
            }
        }
        MySqlCommand Format(string cmdText, MySqlConnection conn, params object[] args)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            MySqlCommand command = new MySqlCommand();
            command.Connection = conn;
            List<string> constants = new List<string>();
            // 调整参数，以供拼凑生成 SQL 语句。
            for (int i = 0; i < args.Length; i++)
            {
                object parameter = args[i];
                // 参数是数字类型时。
                if (parameter is byte || parameter is short || parameter is ushort || parameter is int || parameter is uint || parameter is long || parameter is ulong || parameter is float || parameter is double || parameter is decimal)
                {
                    command.Parameters.Add(CreateParameter( i.ToString("d3"), System.Data.DbType.Double, parameter));
                    continue;
                }
                if (parameter is bool)
                {
                    command.Parameters.Add(CreateParameter( i.ToString("d3"), System.Data.DbType.Boolean, (bool)parameter ? 1 : 0));
                    continue;
                }
                if (parameter is string)
                {
                    command.Parameters.Add(CreateParameter( i.ToString("d3"), System.Data.DbType.String, parameter));
                    continue;
                }
                if (parameter is byte[])
                {
                    command.Parameters.Add(CreateParameter( i.ToString("d3"), System.Data.DbType.Binary, parameter));
                    continue;
                }
                if (parameter is DateTime)
                {
                    command.Parameters.Add(CreateParameter(i.ToString("d3"), System.Data.DbType.DateTime, parameter));
                    continue;
                }
                // 参数是空值类型。
                if (parameter == null || parameter is DBNull)
                {
                    command.Parameters.Add(CreateParameter( i.ToString("d3"), System.Data.DbType.String, DBNull.Value));
                    continue;
                }
                throw new NotSupportedException();
            }
            command.CommandText = string.Format(cmdText, command.Parameters.Cast<IDataParameter>().Select(t => t.ParameterName).ToArray());
            return command;
        }
        MySqlParameter CreateParameter(string name, DbType dbType, object value)
        {
            return new MySqlParameter("?p" + name, value) { DbType = dbType };
        }
    }
}
